﻿using Md.Api.Entity.ModelDB;
using PetaPoco.NetCore;

namespace Md.Petapoco.Repositories.MdTrainingEducation;
/// <summary>
/// 系统菜单仓储
/// </summary>
public class SysMenuRep : BaseRep<SysMenuModel>
{
    /// <summary>
    /// 通过菜单编号获取系统菜单信息
    /// </summary>
    /// <param name="menuId">菜单编号</param>
    /// <returns>SysMenuModel</returns>
    public SysMenuModel GetSysMenuByMenuId(long menuId)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select `menu_id`
                  ,`menu_name`
                  ,`parent_id`
                  ,`order_num`
                  ,`path`
                  ,`component`
                  ,`route_params`
                  ,`is_frame`
                  ,`is_cache`
                  ,`menu_type`
                  ,`visible`
                  ,`status`
                  ,`perms`
                  ,`icon`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_menu`
            where 1 = 1 and menu_id = @0
        ");
        #endregion

        return MdTrainingEducationDB.GetInstance().SingleOrDefault<SysMenuModel>(sqlStr, menuId);
    }
    /// <summary>
    /// 获取系统菜单列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>IEnumerable<SysMenuModel></returns>
    public IEnumerable<SysMenuModel> GetSysMenuList(SysMenuModel model, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.MenuId > 0)
        {
            sqlWhere += " and menu_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.MenuName))
        {
            sqlWhere += " and menu_name like @1 ";
        }
        if (model.ParentId > -1)
        {
            sqlWhere += " and parent_id = @2 ";
        }
        if (model.IsFrame > -1)
        {
            sqlWhere += " and is_frame = @3 ";
        }
        if (model.IsCache > -1)
        {
            sqlWhere += " and is_cache = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.MenuType))
        {
            sqlWhere += " and menu_type = @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Visible))
        {
            sqlWhere += " and visible = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @9 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @10 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @11 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `menu_id`
                  ,`menu_name`
                  ,`parent_id`
                  ,`order_num`
                  ,`path`
                  ,`component`
                  ,`route_params`
                  ,`is_frame`
                  ,`is_cache`
                  ,`menu_type`
                  ,`visible`
                  ,`status`
                  ,`perms`
                  ,`icon`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_menu`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Query<SysMenuModel>(
            sqlStr,
            model.MenuId,
            string.Format(@"%{0}%", model.MenuName),
            model.ParentId,
            model.IsFrame,
            model.IsCache,
            model.MenuType,
            model.Visible,
            model.Status,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 分页获取系统菜单列表数据
    /// </summary>
    /// <param name="model">筛选条件</param>
    /// <param name="pageIndex">当前页</param>
    /// <param name="pageSize">每页条数</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒序</param>
    /// <param name="beginTime">开始时间</param>
    /// <param name="endTime">结束时间</param>
    /// <returns>Page<SysMenuModel></returns>
    public Page<SysMenuModel> GetSysMenuPageList(SysMenuModel model, int pageIndex, int pageSize, string? orderBy, bool? isDesc, DateTime? beginTime, DateTime? endTime)
    {
        string sqlWhere = string.Empty;

        #region ===where===
        if (model.MenuId > 0)
        {
            sqlWhere += " and menu_id = @0 ";
        }
        if (!string.IsNullOrWhiteSpace(model.MenuName))
        {
            sqlWhere += " and menu_name like @1 ";
        }
        if (model.ParentId > -1)
        {
            sqlWhere += " and parent_id = @2 ";
        }
        if (model.IsFrame > -1)
        {
            sqlWhere += " and is_frame = @3 ";
        }
        if (model.IsCache > -1)
        {
            sqlWhere += " and is_cache = @4 ";
        }
        if (!string.IsNullOrWhiteSpace(model.MenuType))
        {
            sqlWhere += " and menu_type = @5 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Visible))
        {
            sqlWhere += " and visible = @6 ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and status = @7 ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and create_by = @8 ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and update_by = @9 ";
        }
        if (beginTime.HasValue)
        {
            sqlWhere += " and create_time >= @10 ";
        }
        if (endTime.HasValue)
        {
            sqlWhere += " and create_time <= @11 ";
        }
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select `menu_id`
                  ,`menu_name`
                  ,`parent_id`
                  ,`order_num`
                  ,`path`
                  ,`component`
                  ,`route_params`
                  ,`is_frame`
                  ,`is_cache`
                  ,`menu_type`
                  ,`visible`
                  ,`status`
                  ,`perms`
                  ,`icon`
                  ,`create_by`
                  ,`create_time`
                  ,`update_by`
                  ,`update_time`
                  ,`remark`
            from `sys_menu`
            where 1 = 1 {0} {1}
         ", sqlWhere, GetOrderByDesc(orderBy, isDesc));
        #endregion

        return MdTrainingEducationDB.GetInstance().Page<SysMenuModel>(
            pageIndex,
            pageSize,
            sqlStr,
            model.MenuId,
            string.Format(@"%{0}%", model.MenuName),
            model.ParentId,
            model.IsFrame,
            model.IsCache,
            model.MenuType,
            model.Visible,
            model.Status,
            model.CreateBy,
            model.UpdateBy,
            string.Format(@"{0} 00:00:00", beginTime),
            string.Format(@"{0} 23:59:59", endTime));
    }
    /// <summary>
    /// 通过系统用户编号获取系统菜单列表
    /// </summary>
    /// <param name="userId">用户编号</param>
    /// <returns>IEnumerable<SysMenuModel></returns>
    public IEnumerable<SysMenuModel> GetSysMenuListByUserId(long userId)
    {
        string sqlStr = string.Format(@"
            select distinct m.menu_id, m.parent_id, m.menu_name, m.path, m.component, m.visible, m.status, ifnull(m.perms,'') as perms, m.is_frame, m.is_cache, m.menu_type, m.icon, m.order_num, m.create_time
            from sys_menu m
            left join sys_role_menu rm on m.menu_id = rm.menu_id
            left join sys_user_role ur on rm.role_id = ur.role_id
            left join sys_role ro on ur.role_id = ro.role_id
            where ur.user_id = @0
        ");

        return MdTrainingEducationDB.GetInstance().Query<SysMenuModel>(sqlStr, userId);
    }
    /// <summary>
    /// 通过菜单编号更新系统菜单信息
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>int</returns>
    public long UpdateSysMenuByMenuId(SysMenuModel model)
    {
        var sql = new Sql();

        sql.Append(" update `sys_menu` set ");

        #region ===set===
        if (!string.IsNullOrWhiteSpace(model.MenuName))
        {
            sql.Append(" menu_name = @0, ", model.MenuName);
        }

        if (model.ParentId > -1)
        {
            sql.Append(" parent_id = @0, ", model.ParentId);
        }

        if (model.OrderNum > -1)
        {
            sql.Append(" order_num = @0, ", model.OrderNum);
        }

        if (!string.IsNullOrWhiteSpace(model.Path))
        {
            sql.Append(" path = @0, ", model.Path);
        }

        if (!string.IsNullOrWhiteSpace(model.Component))
        {
            sql.Append(" component = @0, ", model.Component);
        }

        if (!string.IsNullOrWhiteSpace(model.RouteParams))
        {
            sql.Append(" route_params = @0, ", model.RouteParams);
        }

        if (model.IsFrame > -1)
        {
            sql.Append(" is_frame = @0, ", model.IsFrame);
        }

        if (model.IsCache > -1)
        {
            sql.Append(" is_cache = @0, ", model.IsCache);
        }

        if (!string.IsNullOrWhiteSpace(model.MenuType))
        {
            sql.Append(" menu_type = @0, ", model.MenuType);
        }

        if (!string.IsNullOrWhiteSpace(model.Visible))
        {
            sql.Append(" visible = @0, ", model.Visible);
        }

        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sql.Append(" status = @0, ", model.Status);
        }

        if (!string.IsNullOrWhiteSpace(model.Perms))
        {
            sql.Append(" perms = @0, ", model.Perms);
        }

        if (!string.IsNullOrWhiteSpace(model.Icon))
        {
            sql.Append(" icon = @0, ", model.Icon);
        }

        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sql.Append(" update_by = @0, ", model.UpdateBy);
        }

        if (!string.IsNullOrWhiteSpace(model.Remark))
        {
            sql.Append(" remark = @0, ", model.Remark);
        }

        sql.Append(" update_time = sysdate() ");
        #endregion

        sql.Append(" where 1 = 1 and menu_id = @0 ", model.MenuId);

        return MdTrainingEducationDB.GetInstance().Execute(sql);
    }
}